﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using BadmintonApp.Entity;
using BadmintonApp.DAO.Interface;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace BadmintonApp.DAO.Implement
{
    public class UsersDAO : BaseDAO, IUsersDAO
    {
        /// <summary>
        /// 默认的构造函数
        /// </summary>
        public UsersDAO() : base()
        {
        }

        private int GetMaxUserID()
        {
            string sql = "Select IsNull(Max(FUserID), 0) as FUserID from TUsers";
            DbCommand selectCmd = db.GetSqlStringCommand(sql);
            return (int)db.ExecuteScalar(selectCmd);
        }

        /// <summary>
        /// 插入用户信息
        /// </summary>
        /// <param name="userInfo">用户信息类</param>
        public bool InsertUser(TUsersInfo userInfo)
        {
            StringBuilder sbSQL = new StringBuilder();
            sbSQL.Append("Insert into TUsers(FUserID, FUserName, FDisplayName, FCompany, ");
            sbSQL.Append(" FDepartment, FOffice, FTitle, FMoney, FPassword) Values (");
            sbSQL.Append(" @FUserID, @FUserName, @FDisplayName, @FCompany, @FDepartment, ");
            sbSQL.Append(" @FOffice, @FTitle, @FMoney, @FPassword)");

            DbCommand insertCmd = db.GetSqlStringCommand(sbSQL.ToString());
            userInfo.FUserID = GetMaxUserID() + 1;

            db.AddInParameter(insertCmd, "FUserID", DbType.Int32, userInfo.FUserID);
            db.AddInParameter(insertCmd, "FUserName", DbType.String, userInfo.FUserName);
            db.AddInParameter(insertCmd, "FDisplayName", DbType.String, userInfo.FDisplayName);
            db.AddInParameter(insertCmd, "FCompany", DbType.String, userInfo.FCompany);
            db.AddInParameter(insertCmd, "FDepartment", DbType.String, userInfo.FDepartment);
            db.AddInParameter(insertCmd, "FOffice", DbType.String, userInfo.FOffice);
            db.AddInParameter(insertCmd, "FTitle", DbType.String, userInfo.FTitle);
            db.AddInParameter(insertCmd, "FMoney", DbType.Decimal, userInfo.FMoney);
            db.AddInParameter(insertCmd, "FPassword", DbType.String, userInfo.FPassword);

            int iResult = db.ExecuteNonQuery(insertCmd);
            return true;
        }

        /// <summary>
        /// 更新用户信息
        /// </summary>
        /// <param name="userInfo">用户信息类</param>
        public bool UpdateUser(TUsersInfo userInfo)
        {
            StringBuilder sbSQL = new StringBuilder();
            sbSQL.Append("Update TUsers Set FUserName=@FUserName, FDisplayName=@FDisplayName, FCompany=@FCompany,");
            sbSQL.Append(" FDepartment=@FDepartment, FOffice=@FOffice, FTitle=@FTitle, FMoney=@FMoney, ");
            sbSQL.Append(" FPassword=@FPassword Where FUserID=@FUserID");

            DbCommand updateCmd = db.GetSqlStringCommand(sbSQL.ToString());
            db.AddInParameter(updateCmd, "FUserID", DbType.Int32, userInfo.FUserID);
            db.AddInParameter(updateCmd, "FUserName", DbType.String, userInfo.FUserName);
            db.AddInParameter(updateCmd, "FDisplayName", DbType.String, userInfo.FDisplayName);
            db.AddInParameter(updateCmd, "FCompany", DbType.String, userInfo.FCompany);
            db.AddInParameter(updateCmd, "FDepartment", DbType.String, userInfo.FDepartment);
            db.AddInParameter(updateCmd, "FOffice", DbType.String, userInfo.FOffice);
            db.AddInParameter(updateCmd, "FTitle", DbType.String, userInfo.FTitle);
            db.AddInParameter(updateCmd, "FMoney", DbType.Decimal, userInfo.FMoney);
            db.AddInParameter(updateCmd, "FPassword", DbType.String, userInfo.FPassword);

            int iResult = db.ExecuteNonQuery(updateCmd);
            return true;
        }

        /// <summary>
        /// 删除用户信息
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public bool DeleteUser(int userID)
        {
            string sql = "Delete from TUsers Where FUserID=@FUserID";
            DbCommand deleteCmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(deleteCmd, "FUserID", DbType.Int32, userID);

            int iResult = db.ExecuteNonQuery(deleteCmd);
            return true;
        }

        /// <summary>
        /// 根据用户名获取用户信息
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <returns>用户信息类</returns>
        public TUsersInfo GerUserInfo(string userName)
        {
            string sql = "Select * from TUsers Where FUserName=@FUserName";
            DbCommand getUserInfoCmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(getUserInfoCmd, "FUserName", DbType.String, userName);

            TUsersInfo userInfo = null;
            DataSet dsUser = db.ExecuteDataSet(getUserInfoCmd);
            if (dsUser.Tables.Count > 0 && dsUser.Tables[0] != null)
            {
                DataRow dr = dsUser.Tables[0].Rows[0];
                userInfo.FUserID = Convert.ToInt32(dr["FUserID"]);
                userInfo.FUserName = dr["FUserName"].ToString();
                userInfo.FPassword = dr["FPassword"].ToString();
                userInfo.FDisplayName = dr["FDisplayName"].ToString();
                userInfo.FCompany = dr["FCompany"].ToString();
                userInfo.FDepartment = dr["FDepartment"].ToString();
                userInfo.FMemo = dr["FMemo"].ToString();
                userInfo.FOffice = dr["FOffice"].ToString();
                userInfo.FTitle = dr["FTitle"].ToString();
                if (dr["FMoney"] != null && dr["FMoney"] != DBNull.Value)
                {
                    userInfo.FMoney = Convert.ToDecimal(dr["FMoney"]);
                }
            }
            return userInfo;
        }

        /// <summary>
        /// 根据用户名和密码获取用户信息
        /// </summary>
        /// <param name="userName">用户名</param>
        /// <param name="password">密码</param>
        /// <returns>用户信息类</returns>
        public TUsersInfo GetUserInfoByNameAndPassword(string userName, string password)
        {
            string sql = "Select * from TUsers Where FUserName=@FUserName and FPassword=@FPassword";
            DbCommand getUserInfoByNameAndPasswordCmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(getUserInfoByNameAndPasswordCmd, "FUserName", DbType.String, userName);
            db.AddInParameter(getUserInfoByNameAndPasswordCmd, "FPassword", DbType.String, password);

            TUsersInfo userInfo = null;
            DataSet dsUser = db.ExecuteDataSet(getUserInfoByNameAndPasswordCmd);
            if (dsUser.Tables.Count > 0 && dsUser.Tables[0] != null)
            {
                DataRow dr = dsUser.Tables[0].Rows[0];
                userInfo.FUserID = Convert.ToInt32(dr["FUserID"]);
                userInfo.FUserName = dr["FUserName"].ToString();
                userInfo.FPassword = dr["FPassword"].ToString();
                userInfo.FDisplayName = dr["FDisplayName"].ToString();
                userInfo.FCompany = dr["FCompany"].ToString();
                userInfo.FDepartment = dr["FDepartment"].ToString();
                userInfo.FMemo = dr["FMemo"].ToString();
                userInfo.FOffice = dr["FOffice"].ToString();
                userInfo.FTitle = dr["FTitle"].ToString();
                if (dr["FMoney"] != null)
                {
                    userInfo.FMoney = Convert.ToDecimal(dr["FMoney"]);
                }
            }
            return userInfo;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllUsers()
        {
            string sqlQuery = "Select * from TUsers";
            DbCommand selectCmd = db.GetSqlStringCommand(sqlQuery);
            return db.ExecuteDataSet(selectCmd);
        }
    }
}
